package br.com.busgps.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import br.com.busgps.bean.Bus;
import br.com.busgps.connection.ConnectionDB;
import br.com.busgps.dao.BusDAO;

public class BusDAOImpl extends BusDAO
{
    private static Logger logger = LoggerFactory.getLogger(BusDAOImpl.class);

    private Connection connection;

    public BusDAOImpl()
    {
        connection = ConnectionDB.createConnection();
    }

    /*
     * (non-Javadoc)
     * 
     * @see br.com.busgps.dao.BusDAO#getAllNumbers()
     */
    @Override
    public List<Bus> getAllNumbers()
    {
        List<Bus> buses = new ArrayList<Bus>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try
        {
            String sqlSelect = "SELECT * FROM busgps.bus";

            preparedStatement = connection.prepareStatement(sqlSelect);

            resultSet = preparedStatement.executeQuery(sqlSelect);

            while (resultSet.next())
            {
                long id = resultSet.getLong("id");
                String ordem = resultSet.getString("ordem");
                String number = resultSet.getString("number");

                Bus bus = new Bus(id, ordem, number);

                buses.add(bus);
            }

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar atualizar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);
        }

        return buses;
    }

    /*
     * (non-Javadoc)
     * 
     * @see br.com.busgps.dao.BusDAO#getByNumber(java.lang.String)
     */
    @Override
    public List<Bus> getByNumber(String number)
    {
        List<Bus> buses = new ArrayList<Bus>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try
        {
            String sqlSelect = "SELECT * FROM busgps.bus where number = ?";

            preparedStatement = connection.prepareStatement(sqlSelect);

            preparedStatement.setString(1, number);

            resultSet = preparedStatement.executeQuery(sqlSelect);

            while (resultSet.next())
            {
                long id = resultSet.getLong("id");
                String ordem = resultSet.getString("ordem");
                String numberBus = resultSet.getString("number");

                Bus bus = new Bus(id, ordem, numberBus);

                buses.add(bus);
            }

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar atualizar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);
        }

        return buses;
    }

    /*
     * (non-Javadoc)
     * 
     * @see br.com.busgps.dao.BusDAO#getByOrdem(java.lang.String)
     */
    @Override
    public Bus getByOrdem(String ordem)
    {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try
        {
            String sqlSelect = "SELECT * FROM busgps.bus where ordem = ?";

            preparedStatement = connection.prepareStatement(sqlSelect);

            preparedStatement.setString(1, ordem);

            resultSet = preparedStatement.executeQuery(sqlSelect);

            long id = resultSet.getLong("id");
            String ordemBus = resultSet.getString("ordem");
            String number = resultSet.getString("number");

            Bus bus = new Bus(id, ordemBus, number);

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);

            return bus;
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar atualizar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);
        }

        return null;
    }

    @Override
    public boolean insert(List<Bus> buses)
    {
        PreparedStatement preparedStatement = null;

        try
        {
            String sqlInsert = "INSERT INTO busgps.bus(ordem, number) values(?, ?)";

            preparedStatement = connection.prepareStatement(sqlInsert);

            for (Bus bus : buses)
            {
                preparedStatement.setString(1, bus.getOrdem());
                preparedStatement.setString(2, bus.getNumber());

                preparedStatement.execute(sqlInsert);
            }

            ConnectionDB.closeConnection(connection, preparedStatement);

            return true;
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar salvar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement);
        }

        return false;
    }

    @Override
    public Long insert(Bus bus)
    {
        PreparedStatement preparedStatement = null;

        try
        {
            String sqlInsert = "INSERT INTO busgps.bus(ordem, number) values(?, ?)";

            preparedStatement = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS);

            preparedStatement.setString(1, bus.getOrdem());
            preparedStatement.setString(2, bus.getNumber());

            preparedStatement.execute(sqlInsert);

            ResultSet rs = preparedStatement.getGeneratedKeys();
            
            Long busId = 0L;
            
            if (rs.next())
            {
                busId = rs.getLong(1);
            }

            ConnectionDB.closeConnection(connection, preparedStatement);

            return busId;

        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar salvar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement);
        }

        return null;
    }
}